innodb_rubyでページ情報をダンプしながらオーバーフローページの発生条件について確認してみた
MySQLのオーバーフローページが発生する条件について調べてみました
CX事業本部＠大阪の岩田です。私はOracleとPostgreSQLならある程度分かるのですが、MySQLになるとサッパリ知識がありません。今回MySQLのストレージまわりについて調査する機会があったので、内容をご紹介します。
環境
今回検証に利用した環境です
- MySQL: Community Edition 5.7.33
- Ruby: 2.6.6p146 (2020-03-31 revision 67876) [x86_64-linux]
- Innodb_ruby: 0.9.16
- OS: AmazonLinux2
MySQLの各種パラメータは特に変更せず、yum install後そのままとしています。今回の検証に関連しそうなパラメータは以下の通りです。
- innodb_page_size: 16384
- innodb_file_format: Barracuda
調査の背景
MySQLを使用しているとあるシステムに追加要件で発生し、これまでDBに保持していなかった情報を保持する必要性が出てきました。この追加情報は既存のテーブルと1:Nの関係性を持つデータです。RDBで1:Nのリレーションを扱う場合、別テーブルにデータを持たせて外部キーで親テーブルと結合するのが定番ですが、既存テーブルにJSON型のカラムを追加するという案が出ました。
| id | varchar_col | Json_col |
|---|---|---|
| 1 | hogehoge | {"key1":"val1","key2":"val2","key3":"val3","key4":"val4","key5":"val5"...} |
| 2 | fugafuga | {"key1":"val1","key2":"val2","key3":"val3","key4":"val4","key5":"val5"...} |
こんなイメージです。
RDBでJSON型のカラムを扱う場合はアプリケーションやSQLの保守性・実装容易性など諸々考慮すべき点がありますが、追加カラムで扱う予定のデータが数百要素になることから、ストレージやパフォーマンスへの悪影響も気になりました。MySQLのことは良く分かりませんが、Oracleであれば巨大なカラムを追加することで移行行や連鎖行が発生することに留意すべきです。とりあえずMySQLのドキュメントを読んでみたところ以下の記述を見つけました。
各 InnoDB テーブル内のデータは、ページに分割されます。各テーブルを構成するページは、B ツリーインデックスと呼ばれるツリーデータ構造で配置されます。テーブルデータとセカンダリインデックスはどちらも、このタイプの構造を使用します。テーブル全体を表す B ツリーインデックスは、クラスタ化されたインデックスと呼ばれます。これは、主キーカラムに従って編成されます。インデックスデータ構造のノードには、その行内のすべてのカラム (クラスタ化されたインデックスの場合)、またはインデックスカラムと主キーカラム (セカンダリインデックスの場合) の値が含まれています。
可変長カラムは、このルールの例外です。B ツリーページに収めるには長すぎる
BLOBやVARCHARなどのカラムは、オーバーフローページと呼ばれる、個別に割り当てられたディスクページに格納されます。このようなカラムを、オフページカラムと呼びます。これらのカラムの値は、オーバーフローページの片方向リンクリストに格納され、このような各カラムには 1 つ以上のオーバーフローページの独自のリストがあります。場合によっては、ストレージの浪費を避け、また個別のページを読み取る必要をなくすために、長いカラム値のすべてまたはプリフィクスが B ツリーに格納されます。
※5.7に日本語ドキュメントが見つからなかったので、とりあえず5.6のドキュメントを参照しました
Oracleとの対応で考えるとOracleのブロック≒MySQLのページ、連鎖行≒オーバーフローページと考えると良さそうな印象を受けました。一応社内の技術チャンネルで以下のような質問を投げてみました
色々と回答が返ってきたのですが、1つ気になる回答がありました。
確かに少しググってみたところ、innodb_file_format=BarracudaでVARCHAR等の可変長カラムを利用している場合は必ずオーバーフローページを利用する と解説している記事がいくつかヒットしました。
しかし先程のMySQLのドキュメントには
場合によっては、ストレージの浪費を避け、また個別のページを読み取る必要をなくすために、長いカラム値のすべてまたはプリフィクスが B ツリーに格納されます。
と記載されており、VARCHARのカラムを使ったからといって必ずオーバーフローページを利用するわけでは無いように見えます。このあたりの挙動が気になったので実際に検証用のテーブルを作成して確認してみることにしました。
やってみる
行データがどのようにページに配置されているかを検証するためには、ページの中身を簡単に確認できるツールが必要そうです。少しググってみたところ、innodb_rubyという良さげなツールが見つかりました。検証用のテーブル作成&データ投入後にこのツールでページの中身を確認してみます。
注意点
- 以後の検証結果に
innodb_spaceの実行結果を貼り付けていますが、検証の間に何度かテーブルのDROP&CREATEを実行しているので、検証パターンが変わると出力が微妙に変化している箇所があります。(space-index-pages-summaryで出力されるindexなど) CREATE TABLE実行時に特にオプションを付与していないため、Row Format は全てCOMPACTとなります。
VARCHAR型のカラムに少量のデータを投入した場合
「可変長カラムを利用している場合は常にオーバーフローページを利用する」 という説が本当か検証してみます。以下のSQLを実行し、主キーとVARCHAR型のカラム1つで構成されるテーブルを作成します。
mysql> create table test_tab(id int key, col1 varchar(1000)); Query OK, 0 rows affected (0.01 sec)
適当なレコードをINSERTします。
mysql> insert into test_tab values(1,'hogehoge'); Query OK, 1 row affected (0.00 sec)
レコードサイズはわずかですが、可変長カラムを利用している場合は常にオーバーフローページを利用する動きをするのであれば、2ページに跨ってレコードの情報を保持するはずです。innodb_rubyに含まれるinnodb_spaceを利用して作成したtest_tabテーブルのページ情報を確認します。
$ innodb_space -s ibdata1 -T innodb_test/test_tab space-index-pages-summary page index level data free records 3 67 0 32 16220 1 4 0 0 0 16384 0 5 0 0 0 16384 0
INSERTしたページは3ページ目に保存されていることが分かります。3ページ目以後は利用していないので、オーバーフローページは利用していないようです。3ページ目の中身をダンプしてみます。
$ innodb_space -s ibdata1 -T innodb_test/test_tab -p 3 page-dump
結果は以下のように出力されました。
#<Innodb::Page::Index:0x0000000001dcf5e0>:
fil header:
{:checksum=>2016124931,
:offset=>3,
:prev=>nil,
:next=>nil,
:lsn=>3199955,
:type=>:INDEX,
:flush_lsn=>0,
:space_id=>50}
fil trailer:
{:checksum=>2016124931, :lsn_low32=>3199955}
page header:
{:n_dir_slots=>2,
:heap_top=>152,
:garbage_offset=>0,
:garbage_size=>0,
:last_insert_offset=>127,
:direction=>:no_direction,
:n_direction=>0,
:n_recs=>1,
:max_trx_id=>0,
:level=>0,
:index_id=>67,
:n_heap=>3,
:format=>:compact}
fseg header:
{:leaf=>
<Innodb::Inode space=<Innodb::Space file="innodb_test/test_tab.ibd", page_size=16384, pages=6>, fseg=2>,
:internal=>
<Innodb::Inode space=<Innodb::Space file="innodb_test/test_tab.ibd", page_size=16384, pages=6>, fseg=1>}
sizes:
header 120
trailer 8
directory 4
free 16220
used 164
record 32
per record 32.00
page directory:
[99, 112]
system records:
{:offset=>99,
:header=>
{:next=>127,
:type=>:infimum,
:heap_number=>0,
:n_owned=>1,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>127,
:data=>"infimum\x00",
:length=>8}
{:offset=>112,
:header=>
{:next=>112,
:type=>:supremum,
:heap_number=>1,
:n_owned=>2,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>112,
:data=>"supremum",
:length=>8}
garbage records:
records:
{:format=>:compact,
:offset=>127,
:header=>
{:next=>112,
:type=>:conventional,
:heap_number=>2,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{"col1"=>8},
:externs=>[],
:length=>7},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"INT", :value=>1}],
:row=>[{:name=>"col1", :type=>"VARCHAR(1000)", :value=>"hogehoge"}],
:sys=>
[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>2062},
{:name=>"DB_ROLL_PTR",
:type=>"ROLL_PTR",
:value=>
{:is_insert=>true, :rseg_id=>43, :undo_log=>{:page=>287, :offset=>272}}}],
:length=>25,
:transaction_id=>2062,
:roll_pointer=>
{:is_insert=>true, :rseg_id=>43, :undo_log=>{:page=>287, :offset=>272}}}
注目したいのは行ヘッダのexternsです。
:header=>
{:next=>112,
:type=>:conventional,
:heap_number=>2,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{"col1"=>8},
:externs=>[],
:length=>7},
:externs=>[],となっており、オーバーフローページによって発生したオフセットカラムが存在しないことが分かります。
レコードの中身もダンプしてみます。
$innodb_space -s ibdata1 -T innodb_test/test_tab -p 3 -R 127 record-dump
Record at offset 127
Header:
Next record offset : 112
Heap number : 2
Type : conventional
Deleted : false
Length : 7
System fields:
Transaction ID: 2062
Roll Pointer:
Undo Log: page 287, offset 272
Rollback Segment ID: 43
Insert: true
Key fields:
id: 1
Non-key fields:
col1: "hogehoge"
VARCHAR型のカラムに768バイト以上のデータを投入した場合
「可変長カラムを利用している場合は常にオーバーフローページを利用する」という説は誤りということが分かりました。改めてMySQLのドキュメントを確認したところ、以下の記述が見つかりました。
Tables that use the COMPACT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages. Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.
If the value of a column is 768 bytes or less, an overflow page is not used, and some savings in I/O may result, since the value is stored entirely in the B-tree node. This works well for relatively short BLOB column values, but may cause B-tree nodes to fill with data rather than key values, reducing their efficiency. Tables with many BLOB columns could cause B-tree nodes to become too full, and contain too few rows, making the entire index less efficient than if rows were shorter or column values were stored off-page.
やはり英語版のドキュメントを確認するのは重要ですね。。。列の値が768バイト以下の場合はオーバーフローページは利用しないようです。というわけでVARCHAR型のカラムに768バイト以上のデータを投入してみます。
一度テーブルを作り直して
mysql> drop table test_tab; Query OK, 0 rows affected (0.00 sec) mysql> create table test_tab(id int key, col1 varchar(1000)); Query OK, 0 rows affected (0.01 sec)
データを投入
insert into test_tab values(1,REPEAT('1', 800));
再度test_tabテーブルのページ情報を確認してみます。768バイト以上のデータを投入したので、オーバーフローページが発生するのが期待値です。
$ innodb_space -s ibdata1 -T innodb_test/test_tab space-index-pages-summary page index level data free records 3 69 0 825 15427 1 4 0 0 0 16384 0 5 0 0 0 16384 0
あれ？相変わらず3ページ目しか利用していません。
3ページ目のダンプを出力して確認します。
# innodb_space -s ibdata1 -T innodb_test/test_tab -p 3 page-dump
#<Innodb::Page::Index:0x0000000001323ad8>:
fil header:
{:checksum=>1109174242,
:offset=>3,
:prev=>nil,
:next=>nil,
:lsn=>3214175,
:type=>:INDEX,
:flush_lsn=>0,
:space_id=>52}
fil trailer:
{:checksum=>1109174242, :lsn_low32=>3214175}
page header:
{:n_dir_slots=>2,
:heap_top=>945,
:garbage_offset=>0,
:garbage_size=>0,
:last_insert_offset=>128,
:direction=>:no_direction,
:n_direction=>0,
:n_recs=>1,
:max_trx_id=>0,
:level=>0,
:index_id=>69,
:n_heap=>3,
:format=>:compact}
fseg header:
{:leaf=>
<Innodb::Inode space=<Innodb::Space file="innodb_test/test_tab.ibd", page_size=16384, pages=6>, fseg=2>,
:internal=>
<Innodb::Inode space=<Innodb::Space file="innodb_test/test_tab.ibd", page_size=16384, pages=6>, fseg=1>}
sizes:
header 120
trailer 8
directory 4
free 15427
used 957
record 825
per record 825.00
page directory:
[99, 112]
system records:
{:offset=>99,
:header=>
{:next=>128,
:type=>:infimum,
:heap_number=>0,
:n_owned=>1,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>128,
:data=>"infimum\x00",
:length=>8}
{:offset=>112,
:header=>
{:next=>112,
:type=>:supremum,
:heap_number=>1,
:n_owned=>2,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>112,
:data=>"supremum",
:length=>8}
garbage records:
records:
{:format=>:compact,
:offset=>128,
:header=>
{:next=>112,
:type=>:conventional,
:heap_number=>2,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{"col1"=>800},
:externs=>[],
:length=>8},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"INT", :value=>1}],
:row=>
[{:name=>"col1",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"}],
:sys=>
[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>2083},
{:name=>"DB_ROLL_PTR",
:type=>"ROLL_PTR",
:value=>
{:is_insert=>true, :rseg_id=>58, :undo_log=>{:page=>303, :offset=>272}}}],
:length=>817,
:transaction_id=>2083,
:roll_pointer=>
{:is_insert=>true, :rseg_id=>58, :undo_log=>{:page=>303, :offset=>272}}}
externsが空配列なので、VARCHAR型のカラムcol1の値も全てBツリーノードに格納されているようです。
一応レコードの中身も確認
$ innodb_space -s ibdata1 -T innodb_test/test_tab -p 3 -R 127 record-dump
Record at offset 127
Header:
Next record offset : 4478
Heap number : 0
Type : conventional
Deleted : false
Length : 6
System fields:
Transaction ID: 1099511627784
Roll Pointer:
Undo Log: page 3120562177, offset 12033
Rollback Segment ID: 35
Insert: false
Key fields:
id: 1887436800
Non-key fields:
col1: :NULL
768バイト以上のデータを投入したのになぜ....
レコードサイズが8126バイトを超えるようにデータを投入した場合
さらにネット上の情報を探したところ、以下のMySQL Serverの開発者による以下のブログを見つけました。
Externally Stored Fields in InnoDB
要約すると以下のような内容が記載されていました。
- ページサイズが16KでCOMPACT Row Formatの場合、1レコードのサイズ上限は8126バイトになる
- レコードサイズが上記の上限を超過する場合は、一部カラムの値を別のページに格納する(オーバーフローページ)
- オーバーフローページの対象を選定する際は、以下の前提条件がある
- 固定長のフィールドはオーバーフロー対象として選定されない
- サイズが40バイト以下の可変長フィールドはオーバーフロー対象として選定されない
- フィールドデータ長がBLOBプレフィックスのサイズ(768バイト)以下の可変長フィールドはオーバーフロー対象として選定されない
- クラスタ化インデックスのレコードサイズが8126バイト以下になるように、上記のルールに基づいてサイズの大きいフィールドから順に別のページへ移動する
- ※InnoDBの主キーはクラスタ化インデックスになっており、リーフノードに各フィールドのデータを保持しています。
ということは、レコードサイズが8126バイトを超えるようなデータを投入すればオーバーフローページが発生しそうです。8126バイト以上のレコードを投入できるように少し定義を変更してテーブルを再作成します。
mysql> drop table test_tab; Query OK, 0 rows affected (0.00 sec) mysql> create table test_tab(id int key, col1 varchar(10000),col2 varchar(10000)); Query OK, 0 rows affected (0.01 sec)
レコードサイズが8126バイトを超えるようなデータを投入します。
mysql> insert into test_tab values(1,REPEAT('1', 8126), REPEAT('1',1));
Query OK, 1 row affected (0.00 sec)
col1に大量の文字列を登録していますが、col2に登録する文字列は'1'だけです。col1だけを別ページに移動すれば8126バイトの制限を回避できそうです。実際にページの状況を確認してみましょう。
$ innodb_space -s ibdata1 -T innodb_test/test_tab space-index-pages-summary page index level data free records 3 73 0 47 16205 1 5 0 0 0 16384 0
space-index-pages-summaryの結果に4ページ目が表示されなくなりました。
$ innodb_space -s ibdata1 -T innodb_test/test_tab space-summary page type prev next lsn 0 FSP_HDR 0 0 3406430 1 IBUF_BITMAP 0 0 3390548 2 INODE 0 0 3406430 3 INDEX 0 0 3406430 4 BLOB 0 0 3406430 5 ALLOCATED 0 0 0
オプションをspace-summaryに変更して4ページ目のtypeを確認するとBLOBとなっています。オーバーフローページに利用するためにtypeがBLOBになったと考えられます。
クラスタ化インデックスとして利用中の3ページ目のダンプを確認します。
$ innodb_space -s ibdata1 -T innodb_test/test_tab -p 3 page-dump
#<Innodb::Page::Index:0x000000000259da60>:
fil header:
{:checksum=>1692089814,
:offset=>3,
:prev=>nil,
:next=>nil,
:lsn=>3406430,
:type=>:INDEX,
:flush_lsn=>0,
:space_id=>56}
fil trailer:
{:checksum=>1692089814, :lsn_low32=>3406430}
page header:
{:n_dir_slots=>2,
:heap_top=>167,
:garbage_offset=>0,
:garbage_size=>0,
:last_insert_offset=>129,
:direction=>:no_direction,
:n_direction=>0,
:n_recs=>1,
:max_trx_id=>0,
:level=>0,
:index_id=>73,
:n_heap=>3,
:format=>:compact}
fseg header:
{:leaf=>
<Innodb::Inode space=<Innodb::Space file="innodb_test/test_tab.ibd", page_size=16384, pages=6>, fseg=2>,
:internal=>
<Innodb::Inode space=<Innodb::Space file="innodb_test/test_tab.ibd", page_size=16384, pages=6>, fseg=1>}
sizes:
header 120
trailer 8
directory 4
free 16205
used 179
record 47
per record 47.00
page directory:
[99, 112]
system records:
{:offset=>99,
:header=>
{:next=>129,
:type=>:infimum,
:heap_number=>0,
:n_owned=>1,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>129,
:data=>"infimum\x00",
:length=>8}
{:offset=>112,
:header=>
{:next=>112,
:type=>:supremum,
:heap_number=>1,
:n_owned=>2,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>112,
:data=>"supremum",
:length=>8}
garbage records:
records:
{:format=>:compact,
:offset=>129,
:header=>
{:next=>112,
:type=>:conventional,
:heap_number=>2,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{"col1"=>20, "col2"=>1},
:externs=>["col1"],
:length=>9},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"INT", :value=>1}],
:row=>
[{:name=>"col1",
:type=>"VARCHAR(10000)",
:value=>"",
:extern=>{:space_id=>56, :page_number=>4, :offset=>38, :length=>8126}},
{:name=>"col2", :type=>"VARCHAR(10000)", :value=>"1"}],
:sys=>
[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>2195},
{:name=>"DB_ROLL_PTR",
:type=>"ROLL_PTR",
:value=>
{:is_insert=>true,
:rseg_id=>104,
:undo_log=>{:page=>433, :offset=>272}}}],
:length=>38,
:transaction_id=>2195,
:roll_pointer=>
{:is_insert=>true, :rseg_id=>104, :undo_log=>{:page=>433, :offset=>272}}}
col1の表示がこれまでとは変わり、:extern=>{:space_id=>56, :page_number=>4, :offset=>38, :length=>8126}となっています。
records:
{:format=>:compact,
:offset=>129,
:header=>
{:next=>112,
:type=>:conventional,
:heap_number=>2,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{"col1"=>20, "col2"=>1},
:externs=>["col1"],
:length=>9},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"INT", :value=>1}],
:row=>
[{:name=>"col1",
:type=>"VARCHAR(10000)",
:value=>"",
:extern=>{:space_id=>56, :page_number=>4, :offset=>38, :length=>8126}},
{:name=>"col2", :type=>"VARCHAR(10000)", :value=>"1"}],
col1に登録された実際の値が4ページ目に登録されていることが分かります。
4ページ目をダンプしてみます
$ innodb_space -s ibdata1 -T innodb_test/test_tab -p 4 page-dump
#<Innodb::Page::Blob:0x00000000021e0e40>:
fil header:
{:checksum=>2277883393,
:offset=>4,
:prev=>0,
:next=>0,
:lsn=>3406430,
:type=>:BLOB,
:flush_lsn=>0,
:space_id=>56}
fil trailer:
{:checksum=>2277883393, :lsn_low32=>3406430}
blob header:
{:length=>8126, :next=>nil}
blob data:
00000000 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 |1111111111111111|
00000016 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 |1111111111111111|
00000032 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 |1111111111111111|
00000048 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 |1111111111111111|
...略
00008080 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 |1111111111111111|
00008096 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 |1111111111111111|
00008112 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 |11111111111111 |
INSERT時にcol1の値として指定したREPEAT('1', 8126)の値が4ページ目に保存されていることが分かります。
各カラムのサイズが768バイト以下でレコードサイズが8126バイトを超えるようにデータを投入した場合
オーバーフローページの条件が分かった気がします。が、先程MySQL Serverの開発者によるブログで確認できたフィールドデータ長がBLOBプレフィックスのサイズ(768バイト)以下の可変長フィールドはオーバーフロー対象として選定されないというルールが気になりました。768バイト以下のカラムが大量に存在し、レコードサイズとしては8126バイトを超える場合はどうなるのでしょうか？オーバーフローページが発生しないならINSERTできずにエラーになるのでしょうか？？確認してみましょう。
VARCHAR型のカラム11個を持つテーブルを作成し
mysql> create table test_tab(id int key, col1 varchar(1000),col2 varchar(1000), col3 varchar(1000), col4 varchar(1000), col5 varchar(1000),col6 varchar(1000), col7 varchar(1000),col8 varchar(1000),col9 varchar(1000),col10 varchar(1000),col11 varchar(1000)); Query OK, 0 rows affected (0.01 sec)
各カラムに767バイトのデータを投入します(微妙なニュアンスの翻訳ミスが嫌だったので768バイトではなく767バイトとしました。)
mysql> insert into test_tab values(1,REPEAT('1', 767), REPEAT('1', 767),REPEAT('1', 767),REPEAT('1', 767),REPEAT('1', 767),REPEAT('1', 767),REPEAT('1', 767),REPEAT('1', 767),REPEAT('1', 767),REPEAT('1', 767),REPEAT('1', 767));
Query OK, 1 row affected (0.00 sec)
INSERTには成功しました。
ページのダンプを確認してみます。
$ innodb_space -s ibdata1 -T innodb_test/test_tab -p 3 page-dump
#<Innodb::Page::Index:0x0000000002a715c8>:
fil header:
{:checksum=>4076105684,
:offset=>3,
:prev=>nil,
:next=>nil,
:lsn=>3499577,
:type=>:INDEX,
:flush_lsn=>0,
:space_id=>59}
fil trailer:
{:checksum=>4076105684, :lsn_low32=>3499577}
page header:
{:n_dir_slots=>2,
:heap_top=>7856,
:garbage_offset=>0,
:garbage_size=>0,
:last_insert_offset=>149,
:direction=>:no_direction,
:n_direction=>0,
:n_recs=>1,
:max_trx_id=>0,
:level=>0,
:index_id=>76,
:n_heap=>3,
:format=>:compact}
fseg header:
{:leaf=>
<Innodb::Inode space=<Innodb::Space file="innodb_test/test_tab.ibd", page_size=16384, pages=6>, fseg=2>,
:internal=>
<Innodb::Inode space=<Innodb::Space file="innodb_test/test_tab.ibd", page_size=16384, pages=6>, fseg=1>}
sizes:
header 120
trailer 8
directory 4
free 8516
used 7868
record 7736
per record 7736.00
page directory:
[99, 112]
system records:
{:offset=>99,
:header=>
{:next=>149,
:type=>:infimum,
:heap_number=>0,
:n_owned=>1,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>149,
:data=>"infimum\x00",
:length=>8}
{:offset=>112,
:header=>
{:next=>112,
:type=>:supremum,
:heap_number=>1,
:n_owned=>2,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>112,
:data=>"supremum",
:length=>8}
garbage records:
records:
{:format=>:compact,
:offset=>149,
:header=>
{:next=>112,
:type=>:conventional,
:heap_number=>2,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>
{"col1"=>20,
"col2"=>767,
"col3"=>767,
"col4"=>767,
"col5"=>767,
"col6"=>767,
"col7"=>767,
"col8"=>767,
"col9"=>767,
"col10"=>767,
"col11"=>767},
:externs=>["col1"],
:length=>29},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"INT", :value=>1}],
:row=>
[{:name=>"col1",
:type=>"VARCHAR(1000)",
:value=>"",
:extern=>{:space_id=>59, :page_number=>4, :offset=>38, :length=>767}},
{:name=>"col2",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col3",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col4",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col5",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col6",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col7",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col8",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col9",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col10",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
{:name=>"col11",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"}],
:sys=>
[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>2243},
{:name=>"DB_ROLL_PTR",
:type=>"ROLL_PTR",
:value=>
{:is_insert=>true, :rseg_id=>42, :undo_log=>{:page=>286, :offset=>272}}}],
:length=>7707,
:transaction_id=>2243,
:roll_pointer=>
{:is_insert=>true, :rseg_id=>42, :undo_log=>{:page=>286, :offset=>272}}}
col1にexternの表示が出ているので、オーバーフローページが発生していそうですね。
:lengths=>
{"col1"=>20,
"col2"=>767,
"col3"=>767,
"col4"=>767,
"col5"=>767,
"col6"=>767,
"col7"=>767,
"col8"=>767,
"col9"=>767,
"col10"=>767,
"col11"=>767},
:externs=>["col1"],
:length=>29},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"INT", :value=>1}],
:row=>
[{:name=>"col1",
:type=>"VARCHAR(1000)",
:value=>"",
:extern=>{:space_id=>59, :page_number=>4, :offset=>38, :length=>767}},
{:name=>"col2",
:type=>"VARCHAR(1000)",
:value=>
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111"},
768バイトのルールが良くわからなくなってきました....768バイト未満のサイズでもオーバーフローページの対象になり得るのであれば、単に「サイズの大きなフィールドから順番にオーバーフロー対象にしていく」というルールで良さそうな気がします。追加で調べてみたところ、MySQL5.6の頃はオーバーフローページ利用時に先頭768バイトはローカルページに保存したままで、残りを別ページに保存する動作だったようです。MySQL5.7からはローカルページには別ページへのポインタ20バイトのみローカルページに保存する動作に変わったようです。確かに参照したMySQL Serverの開発者によるブログはMySQL5.6時代のものでした。
改めて先程出力したページのダンプを確認するとオーバーフローページを利用しているcol1のサイズが20となっていることが分かります。
:lengths=>
{"col1"=>20,
"col2"=>767,
"col3"=>767,
"col4"=>767,
"col5"=>767,
"col6"=>767,
"col7"=>767,
"col8"=>767,
"col9"=>767,
"col10"=>767,
"col11"=>767},
ということはMySQL5.7の公式ドキュメントの記載は誤りで、5.6から5.7への変更点が反映されていないのかもしれませんね。
まとめ
MySQLのページ管理に関して色々と学ぶことができました。ネットの記事では「可変長カラムを利用している場合は常にオーバーフローページを利用する」と解説している記事もあったので(各種のパラメータやRow Format次第ではそうなるのかもしれませんが...)、ネットの記事を鵜呑みにせず自分で検証することの重要さを改めて認識することができました。これまでMySQLは勉強せずに来たのですが、これを機会に少しづつ勉強していきたいと思います。
